﻿﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

namespace BuildingForm
{
    public class ExcelHelper
    {
        #region 员工工资表
        /// <summary>
        /// 劳务人员工资表
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strSheetName"></param>
        /// <param name="oldColumnNamed"></param>
        /// <param name="newColumnNames"></param>
        /// <param name="title"></param>
        /// <param name="team"></param>
        public static void ExportByPersonSalary(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportBySalaryStream(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportBySalaryStream(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            //sheet.SetMargin(MarginType.RightMargin, (double)1.1);
            //sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            //sheet.SetMargin(MarginType.LeftMargin, (double)1);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.1);
            sheet.Footer.Center = "第&P页,共&N页";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(3);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.WrapText = true;
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                        sheet.SetColumnWidth(0, 8 * 256);
                        sheet.SetColumnWidth(5, 22 * 256);
                        //sheet.SetColumnWidth(12, 12 * 256);
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 14;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 14;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;

                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 17));
                        row1.CreateCell(0).SetCellValue(team.Split(',')[1] + "年" + team.Split(',')[2] + "月劳务人员工资表");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        //sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 16 * 30;
                        ////设置第5列宽度
                        //sheet.SetColumnWidth(4, 18 * 256);
                        ////设置第11列宽度
                        //sheet.SetColumnWidth(11, 18 * 256);

                        ////第二行
                        //IRow row2 = sheet.CreateRow(1);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 12));

                        //row2.CreateCell(0).SetCellValue("班组名称："+team+"                                                                                                                          编制日期：" + DateTime.Now.ToString("yyyy-MM-dd"));
                        //row2.Height = 14 * 30;
                        //第二行
                        IRow row2 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 17));
                        row2.CreateCell(0).SetCellValue("项目名称:" + team.Split(',')[3] + "      劳务企业名称：                                          " + "班组名称：" + team.Split(',')[0]);
                        row2.Height = 14 * 30;
                        row2.GetCell(0).CellStyle = headStyle2;

                        //第3行
                        IRow row3 = sheet.CreateRow(2);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 17));
                        row3.CreateCell(0).SetCellValue("劳务授权代表:                                       " + "                                                      编制日期：" + DateTime.Now.ToString("yyyy年MM月dd日"));
                        row3.Height = 14 * 30;
                        row3.GetCell(0).CellStyle = headStyle2;


                        //最后一行
                        int num = Convert.ToInt32(dtSource.Rows.Count) + 4;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 17));

                        IRow rowLast = sheet.CreateRow(num);
                        rowLast.CreateCell(0).SetCellValue("班组长确认签字：	                                  劳务管理员确认签字：                                   项目负责人确认签字：                                        填表时间：                   ");
                        //rowLast.CreateCell(0).SetCellValue("总包单位项目经理审核：                    总包单位项目劳务管理员审核：                              劳务公司现场负责人：                        班组长：	                 ");
                        //row3.Height = 18 * 30;
                        //row3.GetCell(0).CellStyle = headStyle;
                    }
                    rowIndex = 4;
                }
                #endregion
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            //newCell.CellStyle = cellStyle;
                            newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 劳务人员工资补发表
        /// <summary>
        /// 劳务人员工资补发表
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strSheetName"></param>
        /// <param name="oldColumnNamed"></param>
        /// <param name="newColumnNames"></param>
        /// <param name="title"></param>
        /// <param name="team"></param>
        public static void ExportByReissue(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByReissue2(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByReissue2(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.1);
            sheet.Footer.Center = "第&P页,共&N页";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(3);
                        IRow headerRow1 = sheet.CreateRow(4);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        IRow row1 = sheet.CreateRow(3);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 0, 0));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 1, 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 2, 2));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 3, 3));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 4, 4));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 5, 5));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 6, 6));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, oldColumnNames.Length - 3, oldColumnNames.Length - 3));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, oldColumnNames.Length - 2, oldColumnNames.Length - 2));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, oldColumnNames.Length - 1, oldColumnNames.Length - 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 7, oldColumnNames.Length - 4));
                        for(var i=0;i<oldColumnNames .Length ; i++)
                        {
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, i, i));
                        }

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = cellStyle;

                            headerRow1.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow1.GetCell(i).CellStyle = cellStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 280);
                            //if (i > 4 && i < oldColumnNames.Length - 1)
                            //{
                            //    sheet.SetColumnWidth(i, 4 * 280);
                            //}

                        }

                        //headerRow.GetCell(7).SetCellValue("应补发工资");

                        //for (int i = 0; i < oldColumnNames.Length; i++)
                        //{
                        //    headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                        //    headerRow.GetCell(i).CellStyle = headStyle;
                        //    //设置列宽
                        //    sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        //}
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 14;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 14;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;

                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, oldColumnNames.Length - 1));
                        row1.CreateCell(0).SetCellValue("劳务人员工资补发表(" + team.Split(',')[1] + "年" + team.Split(',')[2] + "月——" + team.Split(',')[3] + "月)");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 22 * 30;
                        ////设置第5列宽度
                        //sheet.SetColumnWidth(4, 18 * 256);
                        ////设置第11列宽度
                        //sheet.SetColumnWidth(11, 18 * 256);

                        ////第二行
                        //IRow row2 = sheet.CreateRow(1);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 12));

                        //row2.CreateCell(0).SetCellValue("班组名称："+team+"                                                                                                                          编制日期：" + DateTime.Now.ToString("yyyy-MM-dd"));
                        //row2.Height = 14 * 30;
                        //第二行
                        IRow row2 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, oldColumnNames.Length - 1));
                        row2.CreateCell(0).SetCellValue("项目名称: " + team.Split(',')[4] + "    劳务企业名称：                                " + "班组名称：" + team.Split(',')[0]);
                        row2.Height = 14 * 30;
                        row2.GetCell(0).CellStyle = headStyle2;

                        //第3行
                        IRow row3 = sheet.CreateRow(2);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, oldColumnNames.Length - 1));
                        row3.CreateCell(0).SetCellValue("劳务授权代表:                           " + "                                                      编制日期：" + DateTime.Now.ToString("yyyy年MM月dd日"));
                        row3.Height = 14 * 30;
                        row3.GetCell(0).CellStyle = headStyle2;


                        //最后一行
                        int num = Convert.ToInt32(dtSource.Rows.Count) + 6;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, oldColumnNames.Length - 1));

                        IRow rowLast = sheet.CreateRow(num);
                        rowLast.CreateCell(0).SetCellValue("班组长确认签字：	                                  劳务管理员确认签字：                                   项目负责人确认签字：                                        填表时间：                   ");
                        //rowLast.CreateCell(0).SetCellValue("总包单位项目经理审核：                    总包单位项目劳务管理员审核：                              劳务公司现场负责人：                        班组长：	                 ");
                        //row3.Height = 18 * 30;
                        //row3.GetCell(0).CellStyle = headStyle;

                        //倒数第二行
                        int num2 = Convert.ToInt32(dtSource.Rows.Count) + 5;
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, oldColumnNames.Length - 1));

                        IRow rowLast2 = sheet.CreateRow(num2);
                        rowLast2.CreateCell(0).SetCellValue("合计");

                    }
                    rowIndex = 5;
                }
                #endregion
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            //newCell.CellStyle = cellStyle;
                            newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion


        #region 劳务人员日考勤表
        /// <summary>
        /// 人员进出考勤明细表	
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strFileName"></param>
        /// <param name="strSheetName"></param>
        /// <param name="oldColumnNames"></param>
        /// <param name="newColumnNames"></param>
        public static void ExportByPersonPassInOut(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, List<int> arr, string team, string time)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByPassInOutStream(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, arr, team, time))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByPassInOutStream(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, List<int> arr, string team, string time)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);

            sheet.SetMargin(MarginType.RightMargin, (double)1.1);
            sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            sheet.SetMargin(MarginType.LeftMargin, (double)1.1);
            sheet.SetMargin(MarginType.BottomMargin, (double)1.1);
            sheet.Footer.Center = "第&P页,共&N页";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            int num2 = 0;
            string tempSFZH = string.Empty;

            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 循环
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(3);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                        //sheet.SetColumnWidth(2, (arrColWidth[2] + 1) * 256);
                        sheet.SetColumnWidth(3, (12) * 256);
                        sheet.SetColumnWidth(4, (22) * 256);
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 14;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7));
                        row1.CreateCell(0).SetCellValue("劳务人员日考勤表");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 22 * 30;


                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        //headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 14;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;

                        IRow row2 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 7));
                        row2.CreateCell(0).SetCellValue("项目名称：" + team.Split(',')[1] + "    劳务企业名称：                             " + "班组名称：" + team.Split(',')[0]);
                        row2.GetCell(0).CellStyle = headStyle2;
                        row2.Height = 14 * 30;

                        IRow row3 = sheet.CreateRow(2);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 7));
                        row3.CreateCell(0).SetCellValue("劳务授权代表：                          " + "                                                 考勤日期：" + time);
                        row3.GetCell(0).CellStyle = headStyle2;
                        row3.Height = 14 * 30;


                        //最后一行
                        int num = Convert.ToInt32(dtSource.Rows.Count) + 4;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 7));

                        IRow rowLast = sheet.CreateRow(num);
                        rowLast.CreateCell(0).SetCellValue("班组长签字：		   	      劳务管理员确认签字：                     项目负责人确认签字：                     填表时间：            ");
                        //rowLast.Height = 14 * 30;
                        //rowLast.GetCell(0).CellStyle = headStyle2;
                        //row3.Height = 18 * 30;
                        //row3.GetCell(0).CellStyle = headStyle;
                    }
                    rowIndex = 4;
                }
                #endregion

                //ICellStyle cellStyle = workbook.CreateCellStyle();

                //cellStyle.Alignment = HorizontalAlignment.CENTER;
                //cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                //IFont font2 = workbook.CreateFont();
                //cellStyle.SetFont(font2);
                ////设置单元格边框 
                //cellStyle.BorderBottom = CellBorderType.THIN;
                //cellStyle.BorderLeft = CellBorderType.THIN;
                //cellStyle.BorderRight = CellBorderType.THIN;
                //cellStyle.BorderTop = CellBorderType.THIN;

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);

                string sfzh = row["xm"].ToString();
                if (sfzh != tempSFZH)
                {
                    //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 3, arr[num2 + 1] + 2, 5, 5));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 0, 0));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 1, 1));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 2, 2));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 3, 3));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 4, 4));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 5, 5));
                    // sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 6, 6));
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 4, arr[num2 + 1] + 3, 7, 7));
                    //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(arr[num2] + 3, arr[num2 + 1] + 2, 8, 8));
                    ICell newCell = dataRow.CreateCell(7);
                    newCell.SetCellValue(row["zcsj"].ToString());
                    newCell.CellStyle = cellStyle;
                    tempSFZH = sfzh;
                    num2++;
                }
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    //if (i == 8) continue;
                    ICell newCell = dataRow.CreateCell(i);
                    //if (i == 0)
                    //{
                    //    int val = rowIndex + 3 - num2;
                    //    newCell.SetCellValue(val);
                    //    newCell.CellStyle = cellStyle;
                    //    break;
                    //}

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            //newCell.CellStyle = cellStyle;
                            newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
                #endregion
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 人员花名册
        public static void ExportByEmployeeEntryExit(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.7);
            sheet.SetMargin(MarginType.BottomMargin, (double)0.9);
            //HSSFHeader head = new HSSFHeader();

            //sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B__________公司务工人员（含队长、班组长、农民工）考勤表&B\n" + HSSFHeader.FontSize((short)10) + "班组名称：&U  " + team.Split(',')[0] + "  &U ";
            //sheet.Header.Left = "              \n\n项目名称（全称）：__________";
            //sheet.Header.Right = "               \n\n " + team.Split(',')[1] + "年 " + team.Split(',')[2] + "月";
            //sheet.Footer.Left = "班组长签字：___________  \n             ";
            sheet.Footer.Center = "第&P页,共&N页";
            //sheet.Footer.Right = "用工企业盖章：_________       \n                      ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(3);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                        //sheet.SetColumnWidth(4, 25 * 256);
                        //sheet.SetColumnWidth(6, 20 * 256);
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;

                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 16));
                        row1.CreateCell(0).SetCellValue("劳务人员实名登记台账");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 28 * 30;

                        //第二行
                        IRow row2 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 16));
                        row2.CreateCell(0).SetCellValue("项目名称:" + team.Split(',')[1] + "                      劳务企业名称：                                  " + "班组名称：" + team.Split(',')[0]);
                        row2.Height = 14 * 30;
                        row2.GetCell(0).CellStyle = headStyle2;

                        //第3行
                        IRow row3 = sheet.CreateRow(2);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 16));
                        row3.CreateCell(0).SetCellValue("劳务授权代表:                                                        " + "                                                             创建时间：" + DateTime.Now.Year + "年" + DateTime.Now.Month + "月" + DateTime.Now.Day + "日");
                        row3.Height = 14 * 30;
                        row3.GetCell(0).CellStyle = headStyle2;
                    }
                    rowIndex = 4;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion

        #region 员工工时表

        //员工工时表
        public static MemoryStream ExportByWorkTimeStream(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string title, string TotalNum, string TotalHours, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            #region 右击文件 属性信息
            {


                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //if (HttpContext.Current.Session["realname"] != null)
                //{
                //    si.Author = HttpContext.Current.Session["realname"].ToString();
                //}
                //else
                //{
                //    if (HttpContext.Current.Session["username"] != null)
                //    {
                //        si.Author = HttpContext.Current.Session["username"].ToString();
                //    }
                //}                                       //填加xls文件作者信息   
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }
            #endregion
            int rowIndex = 0;

            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            //font.FontHeightInPoints = 12;
            //font.Boldweight = 700;
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        //IRow headerRow = sheet.CreateRow(0);
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        //headerRow.GetCell(0).CellStyle = headStyle;
                        ////sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(2);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽   
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 12;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        //ICellStyle rowStyle1 = headStyle;
                        //rowStyle1.BorderLeft = CellBorderType.NONE;
                        //rowStyle1.BorderRight = CellBorderType.NONE;
                        //ICellStyle rowStyle2 = headStyle;
                        //rowStyle2.BorderLeft = CellBorderType.NONE;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 7));
                        row1.CreateCell(0).SetCellValue("所属名称");
                        row1.CreateCell(1).SetCellValue(team);

                        for (int i = 0; i < 8; i++)
                        {
                            if (i > 1)
                            {
                                row1.CreateCell(i).SetCellValue("");
                            }
                            row1.GetCell(i).CellStyle = headStyle;
                        }

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 15 * 30;


                        //row1.GetCell(4).CellStyle = headStyle;
                        //row1.GetCell(5).CellStyle = headStyle;
                        //row1.GetCell(6).CellStyle = headStyle;
                        //row1.GetCell(7).CellStyle = headStyle;

                        //第二行
                        IRow row2 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 7));
                        row2.CreateCell(0).SetCellValue("考勤统计时间");
                        row2.CreateCell(1).SetCellValue(title);
                        for (int i = 0; i < 8; i++)
                        {
                            if (i > 1)
                            {
                                row2.CreateCell(i).SetCellValue("");
                            }
                            row2.GetCell(i).CellStyle = headStyle;
                        }
                        row2.Height = 15 * 30;

                        //最后一行
                        int num = Convert.ToInt32(TotalNum) + 3;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 2));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 4, 6));
                        IRow row3 = sheet.CreateRow(num);
                        row3.CreateCell(0).SetCellValue("累计出勤人数");
                        row3.CreateCell(3).SetCellValue(TotalNum);
                        row3.CreateCell(4).SetCellValue("累计工时(小时)");
                        row3.CreateCell(7).SetCellValue(TotalHours);
                        row3.GetCell(0).CellStyle = headStyle;
                        row3.GetCell(3).CellStyle = headStyle;
                        row3.GetCell(4).CellStyle = headStyle;
                        row3.GetCell(7).CellStyle = headStyle;
                        //加框
                        row3.CreateCell(1).SetCellValue("");
                        row3.CreateCell(2).SetCellValue("");
                        row3.CreateCell(5).SetCellValue("");
                        row3.CreateCell(6).SetCellValue("");
                        row3.GetCell(1).CellStyle = headStyle;
                        row3.GetCell(2).CellStyle = headStyle;
                        row3.GetCell(5).CellStyle = headStyle;
                        row3.GetCell(6).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 2));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 4, 6));
                    }
                    rowIndex = 3;
                }
                #endregion
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            //newCell.CellStyle = cellStyle;
                            newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }

        /// <summary>   
        /// 员工工时表  
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        public static void ExportByWorkTime(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string title, string count, string totalHours, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }
            using (MemoryStream ms = ExportByWorkTimeStream(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames, title, count, totalHours, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        #endregion  //最简单的导出表

        #region 工时统计表
        /// <summary>
        /// 工时统计
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strSheetName"></param>
        /// <param name="oldColumnNames"></param>
        /// <param name="newColumnNames"></param>
        /// <param name="title"></param>
        /// <param name="TotalNum"></param>
        /// <param name="TotalHours"></param>
        /// <param name="team"></param>
        /// <returns></returns>
        public static MemoryStream ExportByAttendanceStatisticsStream(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string title, string TotalNum, string TotalHours, string team)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            #region 右击文件 属性信息
            {


                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //if (HttpContext.Current.Session["realname"] != null)
                //{
                //    si.Author = HttpContext.Current.Session["realname"].ToString();
                //}
                //else
                //{
                //    if (HttpContext.Current.Session["username"] != null)
                //    {
                //        si.Author = HttpContext.Current.Session["username"].ToString();
                //    }
                //}                                       //填加xls文件作者信息   
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            //font.FontHeightInPoints = 12;
            //font.Boldweight = 700;
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        //IRow headerRow = sheet.CreateRow(0);
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        //headerRow.GetCell(0).CellStyle = headStyle;
                        ////sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(1);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                            if (i == 0)
                            {
                                sheet.SetColumnWidth(i, 14 * 256);
                            }
                        }
                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽   
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 12;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        //ICellStyle rowStyle1 = headStyle;
                        //rowStyle1.BorderLeft = CellBorderType.NONE;
                        //rowStyle1.BorderRight = CellBorderType.NONE;
                        //ICellStyle rowStyle2 = headStyle;
                        //rowStyle2.BorderLeft = CellBorderType.NONE;

                        //第一行
                        IRow row2 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 7));
                        row2.CreateCell(0).SetCellValue("考勤统计时间");
                        row2.CreateCell(1).SetCellValue(title);
                        for (int i = 0; i < 8; i++)
                        {
                            if (i > 1)
                            {
                                row2.CreateCell(i).SetCellValue("");
                            }
                            row2.GetCell(i).CellStyle = headStyle;
                        }
                        row2.Height = 15 * 30;

                        //最后一行
                        int num = Convert.ToInt32(TotalNum) + 2;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 2));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 4, 6));
                        IRow row3 = sheet.CreateRow(num);
                        row3.CreateCell(0).SetCellValue("累计出勤人数");
                        row3.CreateCell(3).SetCellValue(TotalNum);
                        row3.CreateCell(4).SetCellValue("累计工时(小时)");
                        row3.CreateCell(7).SetCellValue(TotalHours);
                        row3.GetCell(0).CellStyle = headStyle;
                        row3.GetCell(3).CellStyle = headStyle;
                        row3.GetCell(4).CellStyle = headStyle;
                        row3.GetCell(7).CellStyle = headStyle;
                        //加框
                        row3.CreateCell(1).SetCellValue("");
                        row3.CreateCell(2).SetCellValue("");
                        row3.CreateCell(5).SetCellValue("");
                        row3.CreateCell(6).SetCellValue("");
                        row3.GetCell(1).CellStyle = headStyle;
                        row3.GetCell(2).CellStyle = headStyle;
                        row3.GetCell(5).CellStyle = headStyle;
                        row3.GetCell(6).CellStyle = headStyle;
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, 2));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 4, 6));
                    }
                    rowIndex = 2;
                }
                #endregion
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);
                            //newCell.CellStyle = cellStyle;
                            newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }

        /// <summary>   
        /// 工时统计 
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        public static void ExportByAttendanceStatistics(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string title, string count, string totalHours, string team)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }
            using (MemoryStream ms = ExportByAttendanceStatisticsStream(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames, title, count, totalHours, team))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        #endregion

        #region 人员住宿信息管理
        public static MemoryStream ExportByStayInfoStream(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string title, string TotalNum)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            #region 右击文件 属性信息
            {


                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //if (HttpContext.Current.Session["realname"] != null)
                //{
                //    si.Author = HttpContext.Current.Session["realname"].ToString();
                //}
                //else
                //{
                //    if (HttpContext.Current.Session["username"] != null)
                //    {
                //        si.Author = HttpContext.Current.Session["username"].ToString();
                //    }
                //}                                       //填加xls文件作者信息   
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            //IFont font2 = workbook.CreateFont();
            //font.FontHeightInPoints = 12;
            //font.Boldweight = 700;
            //cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        //IRow headerRow = sheet.CreateRow(0);
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        //headerRow.GetCell(0).CellStyle = headStyle;
                        ////sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion

                    //字段头
                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(2);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 10;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                            if (i == 0)
                            {
                                sheet.SetColumnWidth(i, 14 * 256);
                            }
                        }
                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽   
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();

                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;
                        //ICellStyle rowStyle1 = headStyle;
                        //rowStyle1.BorderLeft = CellBorderType.NONE;
                        //rowStyle1.BorderRight = CellBorderType.NONE;
                        //ICellStyle rowStyle2 = headStyle;
                        //rowStyle2.BorderLeft = CellBorderType.NONE;
                        ICellStyle headStyle2 = workbook.CreateCellStyle();

                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font2 = workbook.CreateFont();
                        font2.FontHeightInPoints = 10;
                        //font2.Boldweight = 700;
                        headStyle2.SetFont(font2);

                        //第一行
                        IRow row2 = sheet.CreateRow(0);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 10));
                        row2.CreateCell(0).SetCellValue("中建四局珠海公司东莞分公司");
                        row2.Height = 15 * 30;
                        row2.GetCell(0).CellStyle = headStyle;
                        for (int i = 0; i < 11; i++)
                        {
                            if (i == 0)
                            {
                                row2.GetCell(i).CellStyle = headStyle;
                            }
                            else
                            {
                                row2.CreateCell(i).CellStyle = headStyle;
                            }
                        }
                        //第二行
                        IRow row3 = sheet.CreateRow(1);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 10));
                        row3.CreateCell(0).SetCellValue("虎门万科城项目（一期）工人入住登记表 " + title);
                        row3.Height = 12 * 30;
                        row3.GetCell(0).CellStyle = headStyle2;
                        for (int i = 0; i < 11; i++)
                        {
                            if (i == 0)
                            {
                                row3.GetCell(i).CellStyle = headStyle2;
                            }
                            else
                            {
                                row3.CreateCell(i).CellStyle = headStyle2;
                            }
                        }
                    }
                    rowIndex = 3;
                }
                #endregion
                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //newCell.SetCellValue(dateV);
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }

        /// <summary>   
        /// 人员住宿信息管理
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        public static void ExportByStayInfo(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string title, string count)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }
            using (MemoryStream ms = ExportByStayInfoStream(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames, title, count))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        #endregion

        #region  黑名单管理

        public static void Export(DataTable dt, string strHeaderText, string strFileName, string strSheetName)
        {
            //创建工作薄
            HSSFWorkbook wk = new HSSFWorkbook();
            //创建一个名称为mySheet的表
            ISheet tb = wk.CreateSheet(strSheetName);
            PrintSetUp(tb);
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
            si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
            si.Comments = "admin";      //填加xls文件作者信息   
            si.Title = strHeaderText;               //填加xls文件标题信息   
            si.Subject = strHeaderText;              //填加文件主题信息   
            si.CreateDateTime = DateTime.Now;
            wk.SummaryInformation = si;

            //创建一行，此行为第二行
            IRow row1 = tb.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row1.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName.ToString());
            }
            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                IRow row = tb.CreateRow(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);

                    cell.SetCellValue(dt.Rows[i - 1][j].ToString());
                }
            }
            //IRow row = tb.CreateRow(1);
            //for (int i = 0; i < 20; i++)
            //{
            //    ICell cell = row.CreateCell(i);  //在第二行中创建单元格
            //    cell.SetCellValue(i);//循环往第二行的单元格中添加数据
            //}
            using (FileStream fs = File.OpenWrite(strFileName)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                wk.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
            }
        }
        /// <summary>   
        /// DataTable导出到Excel文件   
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
        public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }
            using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        #region web
        /// <summary>   
        /// DataTable导出到Excel的MemoryStream   
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            #region 右击文件 属性信息
            {
                //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                //dsi.Company = "http://....../";
                //workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                //if (HttpContext.Current.Session["realname"] != null)
                //{
                //    si.Author = HttpContext.Current.Session["realname"].ToString();
                //}
                //else
                //{
                //    if (HttpContext.Current.Session["username"] != null)
                //    {
                //        si.Author = HttpContext.Current.Session["username"].ToString();
                //    }
                //}                                       //填加xls文件作者信息   
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }
            #endregion
            int rowIndex = 0;
            //ICellStyle cellStyle = workbook.CreateCellStyle();

            //cellStyle.Alignment = HorizontalAlignment.CENTER;
            //cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            ////IFont font2 = workbook.CreateFont();
            ////font.FontHeightInPoints = 12;
            ////font.Boldweight = 700;
            ////cellStyle.SetFont(font2);
            ////设置单元格边框 
            //cellStyle.BorderBottom = CellBorderType.THIN;
            //cellStyle.BorderLeft = CellBorderType.THIN;
            //cellStyle.BorderRight = CellBorderType.THIN;
            //cellStyle.BorderTop = CellBorderType.THIN;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        //IRow headerRow = sheet.CreateRow(0);
                        //headerRow.HeightInPoints = 25;
                        //headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        //ICellStyle headStyle = workbook.CreateCellStyle();
                        //headStyle.Alignment = HorizontalAlignment.CENTER;
                        //IFont font = workbook.CreateFont();
                        //font.FontHeightInPoints = 20;
                        //font.Boldweight = 700;
                        //headStyle.SetFont(font);

                        //headerRow.GetCell(0).CellStyle = headStyle;
                        ////sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow(0);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
                        }
                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽   
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }
                    #endregion

                    rowIndex = 1;
                }
                #endregion


                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    newCell.SetCellValue(drValue);
                    //switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    //{
                    //    case "System.String"://字符串类型   
                    //        newCell.SetCellValue(drValue);
                    //        break;
                    //    case "System.DateTime"://日期类型   
                    //        DateTime dateV;
                    //        DateTime.TryParse(drValue, out dateV);
                    //        newCell.SetCellValue(dateV);

                    //        newCell.CellStyle = dateStyle;//格式化显示   
                    //        break;
                    //    case "System.Boolean"://布尔型   
                    //        bool boolV = false;
                    //        bool.TryParse(drValue, out boolV);
                    //        newCell.SetCellValue(boolV);
                    //        break;
                    //    case "System.Int16"://整型   
                    //    case "System.Int32":
                    //    case "System.Int64":
                    //    case "System.Byte":
                    //        int intV = 0;
                    //        int.TryParse(drValue, out intV);
                    //        newCell.SetCellValue(intV);
                    //        break;
                    //    case "System.Decimal"://浮点型   
                    //    case "System.Double":
                    //        double doubV = 0;
                    //        double.TryParse(drValue, out doubV);
                    //        newCell.SetCellValue(doubV);
                    //        break;
                    //    case "System.DBNull"://空值处理   
                    //        newCell.SetCellValue("");
                    //        break;
                    //    default:
                    //        newCell.SetCellValue("");
                    //        break;
                    //}

                }
                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }

        #endregion

        /// <summary>   
        /// WEB导出DataTable到Excel   
        /// </summary>   
        /// <param name="dtSource">源DataTable</param>   
        /// <param name="strHeaderText">表头文本</param>   
        /// <param name="strFileName">文件名</param>   
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>   
        //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
        //{
        //    ExportByWeb(dtSource, strHeaderText, strFileName, "sheet");
        //}

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">输出文件名，包含扩展名</param>
        /// <param name="oldColumnNames">要导出的DataTable列数组</param>
        /// <param name="newColumnNames">导出后的对应列名</param>
        //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string[] oldColumnNames, string[] newColumnNames)
        //{
        //    ExportByWeb(dtSource, strHeaderText, strFileName, "sheet", oldColumnNames, newColumnNames);
        //}

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">输出文件名</param>
        /// <param name="strSheetName">工作表名称</param>
        //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName)
        //{
        //    HttpContext curContext = HttpContext.Current;

        //    // 设置编码和附件格式   
        //    curContext.Response.ContentType = "application/vnd.ms-excel";
        //    curContext.Response.ContentEncoding = Encoding.UTF8;
        //    curContext.Response.Charset = "";
        //    curContext.Response.AppendHeader("Content-Disposition",
        //        "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

        //    //生成列
        //    string columns = "";
        //    for (int i = 0; i < dtSource.Columns.Count; i++)
        //    {
        //        if (i > 0)
        //        {
        //            columns += ",";
        //        }
        //        columns += dtSource.Columns[i].ColumnName;
        //    }

        //    curContext.Response.BinaryWrite(Export(dtSource, strHeaderText,strSheetName,columns.Split(','),columns.Split(',')).GetBuffer());
        //    curContext.Response.End();

        //}

        /// <summary>
        /// 导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">要导出的DataTable</param>
        /// <param name="strHeaderText">标题文字</param>
        /// <param name="strFileName">文件名，包含扩展名</param>
        /// <param name="strSheetName">工作表名</param>
        /// <param name="oldColumnNames">要导出的DataTable列数组</param>
        /// <param name="newColumnNames">导出后的对应列名</param>
        //public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        //{
        //    HttpContext curContext = HttpContext.Current;

        //    // 设置编码和附件格式   
        //    curContext.Response.ContentType = "application/vnd.ms-excel";
        //    curContext.Response.ContentEncoding = Encoding.UTF8;
        //    curContext.Response.Charset = "";
        //    curContext.Response.AppendHeader("Content-Disposition",
        //        "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

        //    curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames).GetBuffer());
        //    curContext.Response.End();
        //}
        #endregion

        #region 劳务人员月考勤表
        public static void ExportByEmployeeEntryExit3(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team, string Title, string BeginDuration, string EndDuration)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }

            using (MemoryStream ms = ExportByEmployeeEntryExitStream3(dtSource, strHeaderText, strFileName, strSheetName, oldColumnNames, newColumnNames, team, Title, BeginDuration, EndDuration))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        public static MemoryStream ExportByEmployeeEntryExitStream3(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames, string team, string Title, string BeginDuration, string EndDuration)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(strSheetName);
            PrintSetUp(sheet);
            sheet.SetMargin(MarginType.RightMargin, (double)1.1);
            sheet.SetMargin(MarginType.TopMargin, (double)1.1);
            sheet.SetMargin(MarginType.LeftMargin, (double)1);
            sheet.SetMargin(MarginType.BottomMargin, (double)1);
            //HSSFHeader head = new HSSFHeader();

            //sheet.Header.Center = HSSFHeader.FontSize((short)16) + "&B__________公司务工人员（含队长、班组长、农民工）考勤表&B\n" + HSSFHeader.FontSize((short)10) + "班组名称：&U  " + team.Split(',')[0] + "  &U ";
            //sheet.Header.Left = "              \n\n项目名称（全称）：__________";
            //sheet.Header.Right = "               \n\n " + team.Split(',')[1] + "年 " + team.Split(',')[2] + "月";
            //sheet.Footer.Left = "班组长签字：___________  \n             ";
            sheet.Footer.Center = "第&P页,共&N页";
            //sheet.Footer.Right = "用工企业盖章：_________       \n                      ";
            #region 右击文件 属性信息
            {
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.ApplicationName = strHeaderText;            //填加xls文件创建程序信息   
                si.LastAuthor = strHeaderText;           //填加xls文件最后保存者信息   
                si.Comments = "admin";      //填加xls文件作者信息   
                si.Title = strHeaderText;               //填加xls文件标题信息   
                si.Subject = strHeaderText;              //填加文件主题信息   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.Alignment = HorizontalAlignment.CENTER;
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            dateStyle.BorderBottom = CellBorderType.THIN;
            dateStyle.BorderLeft = CellBorderType.THIN;
            dateStyle.BorderRight = CellBorderType.THIN;
            dateStyle.BorderTop = CellBorderType.THIN;
            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion
            int rowIndex = 0;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            IFont font2 = workbook.CreateFont();
            cellStyle.SetFont(font2);
            //设置单元格边框 
            cellStyle.BorderBottom = CellBorderType.THIN;
            cellStyle.BorderLeft = CellBorderType.THIN;
            cellStyle.BorderRight = CellBorderType.THIN;
            cellStyle.BorderTop = CellBorderType.THIN;
            //long totalCount = 0;
            for (var j = 0; j < dtSource.Rows.Count; j++)
            {
                DataRow row = dtSource.Rows[j];
                //}
                //foreach (DataRow row in dtSource.Rows)
                //{

                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
                    }

                    #region 表头及样式
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //headStyle.BorderBottom = CellBorderType.THIN;
                        //headStyle.BorderLeft = CellBorderType.THIN;
                        //headStyle.BorderRight = CellBorderType.THIN;
                        //headStyle.BorderTop = CellBorderType.THIN;
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;
                    }
                    #endregion


                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow1 = sheet.CreateRow(4);
                        IRow headerRow = sheet.CreateRow(3);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        headStyle.BorderBottom = CellBorderType.THIN;
                        headStyle.BorderLeft = CellBorderType.THIN;
                        headStyle.BorderRight = CellBorderType.THIN;
                        headStyle.BorderTop = CellBorderType.THIN;





                        IRow row1 = sheet.CreateRow(3);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 0, 0));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 1, 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 2, 2));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 3, 3));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 4, 4));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, 5, 5));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 4, oldColumnNames.Length - 1, oldColumnNames.Length - 1));
                        if (Title.Split('|').Length > 1)
                        {
                            int begin1 = Convert.ToInt32(BeginDuration.Split('|')[0]);
                            int end1 = Convert.ToInt32(BeginDuration.Split('|')[1]);
                            int dur1 = end1 - begin1 + 1;
                            int begin2 = Convert.ToInt32(EndDuration.Split('|')[0]);
                            int end2 = Convert.ToInt32(EndDuration.Split('|')[1]);
                            int dur2 = end2 - begin2 + 1;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 6, 5 + dur1));
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 6 + dur1, oldColumnNames.Length - 2));
                            //string t1 = Title.Split('|')[0];
                            //string t2 = Title.Split('|')[1];                            
                            //headerRow.GetCell(6).SetCellValue(t1);
                            //headerRow.GetCell(7 + dur1).SetCellValue(t2);
                        }
                        else
                        {
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 6, oldColumnNames.Length - 2));
                            //headerRow.GetCell(6).SetCellValue(Title);
                        }




                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = cellStyle;

                            headerRow1.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow1.GetCell(i).CellStyle = cellStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 280);
                            if (i > 5 && i < oldColumnNames.Length - 1)
                            {
                                sheet.SetColumnWidth(i, 4 * 280);
                            }

                        }
                        if (Title.Split('|').Length > 1)
                        {
                            int begin1 = Convert.ToInt32(BeginDuration.Split('|')[0]);
                            int end1 = Convert.ToInt32(BeginDuration.Split('|')[1]);
                            int dur1 = end1 - begin1 + 1;
                            string t1 = Title.Split('|')[0];
                            string t2 = Title.Split('|')[1];
                            headerRow.GetCell(6).SetCellValue(t1);
                            headerRow.GetCell(6 + dur1).SetCellValue(t2);
                        }
                        else
                        {
                            headerRow.GetCell(6).SetCellValue(Title);
                        }

                        //headerRow.GetCell(6).SetCellValue("上月");
                        //headerRow.GetCell(12).SetCellValue("本月");
                    }
                    #endregion
                    {
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 16;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置单元格边框 
                        headStyle.BorderBottom = CellBorderType.NONE;
                        headStyle.BorderLeft = CellBorderType.NONE;
                        headStyle.BorderRight = CellBorderType.NONE;
                        headStyle.BorderTop = CellBorderType.NONE;
                        //headStyle.BottomBorderColor =  NPOI.HSSF.Util.HSSFColor.WHITE.index;
                        //headStyle.TopBorderColor = HSSFColor.WHITE.index;
                        ICellStyle headStyle2 = workbook.CreateCellStyle();
                        headStyle2.Alignment = HorizontalAlignment.CENTER;
                        headStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                        IFont nfont = workbook.CreateFont();
                        nfont.FontHeightInPoints = 15;
                        //nfont.Boldweight = 700;
                        headStyle2.SetFont(nfont);
                        //设置单元格边框 
                        headStyle2.BorderBottom = CellBorderType.NONE;
                        headStyle2.BorderLeft = CellBorderType.NONE;
                        headStyle2.BorderRight = CellBorderType.NONE;
                        headStyle2.BorderTop = CellBorderType.NONE;
                        //headStyle2.BottomBorderColor = HSSFColor.WHITE.index;
                        //headStyle2.TopBorderColor = HSSFColor.WHITE.index;
                        //第一行
                        //IRow row1 = sheet.CreateRow(0);
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 1));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 2));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 3));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 9));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 10, 35));
                        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 35, 36));
                        //row1.CreateCell(0).SetCellValue("人员信息档案表");
                        //row1.GetCell(0).CellStyle = headStyle;

                        ////设置第一列宽度
                        //sheet.SetColumnWidth(0, 12 * 256);
                        //row1.Height = 28 * 30;
                        //第一行
                        IRow row1 = sheet.CreateRow(0);
                        row1.RowStyle = headStyle;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, oldColumnNames.Length - 1));
                        row1.CreateCell(0).SetCellValue("劳务人员月考勤表");
                        row1.GetCell(0).CellStyle = headStyle;

                        //设置第一列宽度
                        sheet.SetColumnWidth(0, 12 * 256);
                        row1.Height = 22 * 30;

                        //第二行
                        IRow row2 = sheet.CreateRow(1);
                        row2.RowStyle = headStyle2;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, oldColumnNames.Length - 1));
                        row2.CreateCell(0).SetCellValue("项目名称:" + team.Split(',')[3] + "            劳务企业名称：                                                                    " + "班组名称：" + team.Split(',')[0]);
                        row2.Height = 14 * 30;
                        row2.GetCell(0).CellStyle = headStyle2;

                        //第3行
                        IRow row3 = sheet.CreateRow(2);
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, oldColumnNames.Length - 1));
                        row3.CreateCell(0).SetCellValue("劳务授权代表:                                                       " + "                                                                                                                               考勤月度：" + team.Split(',')[1] + "年" + team.Split(',')[2] + "月");
                        row3.Height = 14 * 30;
                        row3.GetCell(0).CellStyle = headStyle2;

                        //最后一行
                        int num = Convert.ToInt32(dtSource.Rows.Count) + 5;
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(num, num, 0, oldColumnNames.Length - 1));

                        IRow rowLast = sheet.CreateRow(num);
                        rowLast.CreateCell(0).SetCellValue("班组长确认签字：	                                  劳务管理员确认签字：                                   项目负责人确认签字：                                        填表时间：                   ");
                        rowLast.Height = 14 * 30;
                        rowLast.GetCell(0).CellStyle = headStyle2;
                    }
                    rowIndex = 5;
                }
                #endregion

                #region 填充内容

                if (j > 0)
                {
                    //int不能用于计算
                    decimal jj = j;
                    //if ((jj - 33) % 35 == 0)
                    //{
                    //    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 30));
                    //    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 31, 33));
                    //    IRow dataRow1 = sheet.CreateRow(rowIndex);
                    //    for (var i = 0; i < 35; i++)
                    //    {
                    //        dataRow1.CreateCell(i).SetCellValue(" ");
                    //        dataRow1.GetCell(i).CellStyle = cellStyle;
                    //    }
                    //    ICell newCell = dataRow1.GetCell(0);
                    //    newCell.SetCellValue("申明：此表登记务工人员为我单位本月在该工程全部出勤人数，出勤情况属实；我单位已将此表向全体务工人员公示，均无异议。");
                    //    newCell.CellStyle = cellStyle;
                    //    ICell newCell2 = dataRow1.GetCell(31);
                    //    newCell2.SetCellValue("总计");
                    //    newCell2.CellStyle = cellStyle;
                    //    ICell newCell3 = dataRow1.GetCell(34);
                    //    newCell3.SetCellValue(totalCount.ToString());
                    //    newCell3.CellStyle = cellStyle;
                    //    totalCount = 0;
                    //    rowIndex++;
                    //}
                }
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();
                    //if (i == oldColumnNames.Length - 1)
                    //{
                    //    totalCount += Convert.ToInt64(drValue);
                    //}
                    //if (i < oldColumnNames.Length - 1 && i >= 3)
                    //{
                    //    int val = Convert.ToInt32(drValue);
                    //    if (val == 0)
                    //    {
                    //        drValue = string.Empty;
                    //    }
                    //    else
                    //    {
                    //        drValue = "√";
                    //    }
                    //}
                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型   
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            //DateTime dateV;
                            //DateTime.TryParse(drValue, out dateV);
                            //newCell.SetCellValue(dateV);
                            ////newCell.CellStyle = cellStyle;
                            //newCell.CellStyle = dateStyle;//格式化显示   
                            break;
                        case "System.Boolean"://布尔型   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            //int intV = 0;
                            //int.TryParse(drValue, out intV);
                            //newCell.SetCellValue(intV);
                            //newCell.CellStyle = cellStyle;
                            //break;
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型   
                        case "System.Double":
                            //double doubV = 0;
                            //double.TryParse(drValue, out doubV);
                            //newCell.SetCellValue(doubV);
                            //newCell.CellStyle = cellStyle;
                            //break;
                            newCell.SetCellValue(drValue);
                            newCell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理   
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle;
                            break;
                    }

                }

                #endregion
                rowIndex++;
            }

            //最后一行
            //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 30));
            //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 31, 33));
            //IRow dataRow11 = sheet.CreateRow(rowIndex);
            //for (var i = 0; i < 35; i++)
            //{
            //    dataRow11.CreateCell(i).SetCellValue(" ");
            //    dataRow11.GetCell(i).CellStyle = cellStyle;
            //}

            //ICell newCell1 = dataRow11.GetCell(0);
            //newCell1.SetCellValue("申明：此表登记务工人员为我单位本月在该工程全部出勤人数，出勤情况属实；我单位已将此表向全体务工人员公示，均无异议。");
            //ICell newCell21 = dataRow11.GetCell(31);
            //newCell21.SetCellValue("总计");
            //ICell newCell31 = dataRow11.GetCell(34);
            //newCell31.SetCellValue(totalCount.ToString());

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet   
                return ms;
            }
        }
        #endregion


        /// <summary>读取excel   
        /// 默认第一行为表头，导入第一个工作表
        /// </summary>   
        /// <param name="strFileName">excel文档路径</param>   
        /// <returns></returns>   
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                string SheetName = workbook.GetSheetName(SheetIndex);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();
                            switch (cell.CellType)
                            {
                                case CellType.BLANK:
                                    dataRow[j] = null;
                                    break;
                                case CellType.BOOLEAN:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.NUMERIC:
                                    dataRow[j] = cell.ToString();
                                    break;
                                case CellType.STRING:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.ERROR:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.FORMULA:
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();
                }
                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }
            #region 清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            #endregion
            return table;
        }

        public static void PrintSetUp(ISheet sheet)
        {
            sheet.SetMargin(MarginType.RightMargin, (double)0.5);
            sheet.SetMargin(MarginType.TopMargin, (double)0.5);
            sheet.SetMargin(MarginType.LeftMargin, (double)0.5);
            sheet.SetMargin(MarginType.BottomMargin, (double)0.5);
            //打印份数
            sheet.PrintSetup.Copies = 1;
            //单色打印
            sheet.PrintSetup.NoColor = true;
            //横向
            sheet.PrintSetup.Landscape = true;
            sheet.PrintSetup.PaperSize = (short)PaperSize.A4;
            //页高
            sheet.PrintSetup.FitHeight = 1;
            //页宽
            sheet.PrintSetup.FitWidth = 1;
            sheet.IsPrintGridlines = true;

            //是否自适应界面
            sheet.FitToPage = false;
            //设置打印标题
            //workbook.SetRepeatingRowsAndColumns(0, 0, 5, 0, 5);
        }

        //public static void DezaTable()
        //{
        //    foreach (DataColumn column in SourceTable.Columns)
        //    {
        //        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
        //        if (row[column].ToString().Contains("\n"))
        //        {        //將目前欄位的CellStyle設定為自動換行       
        //            HSSFCellStyle cs = workbook.CreateCellStyle();
        //            cs.WrapText = true;
        //           // dataRow.GetCell(column.Ordinal).CellStyle = cs;
        //            //因為換行所以愈設幫他Row的高度變成兩倍     
        //           // dataRow.HeightInPoints = 2 * sheet.DefaultRowHeight / 20;
        //        }
        //    }
        //}
    }
}
